<?php
/**
 * 住所検索用関数<br />
 *
 * @package apamanshop
 * @author　
 * @copyright　
 */
class Search_Model_Selgakko extends Apm_Model_Base{

	/**
	* 学校名一覧データ取得
	*/
	public function getGakkoData($params)
	{
		$db_params = array();
		$sql = $this->generateGakkoSql($params,$db_params);
		return $this->dbGetList($sql,$db_params);
	}

   /**
	* 学校名一覧データを表示用に変換
	*
	* @access	public
	* @param	array ※参照渡し
	* @return	void
	*/
	public function convertGakkoData(&$data)
	{
		$data['Mapping'] = array();

		foreach ($data['gakko'] as $gakkoData)
		{
			$schCd = $gakkoData['T67_SCH_CD'];

			// 市区町村が異なる場合
			if (array_key_exists($schCd, $data['Mapping']) == false)
			{
				$data['Mapping']["$schCd"]['SCH_CD']    = $schCd;
				$data['Mapping']["$schCd"]['SCH_NAME']  = $gakkoData['V03_SCH_NAME'];
				$data['Mapping']["$schCd"]['SHOZAICHI'] = $gakkoData['SHOZAICHI'];
				$data['Mapping']["$schCd"]['JANRU_CD']  = $gakkoData['V03_JANRU_CD'];
			}
		}
	}

	/**
	 * 学校名データ検索用SQL文の取得
	 *
	 * 学校名一覧データ検索用SQL文の取得を行う
	 * @access	public
	 * @param	array
	 * @param	array
	 * @return	string 町名データ検索用SQL
	 */
	public function generateGakkoSql($params,&$db_params)
	{
		$sql = "";
		$sql .= "SELECT ";
		$sql .= "    b.T67_SCH_CD, ";
		$sql .= "    a.V03_SCH_NAME, ";
		$sql .= "    a.V03_JANRU_CD, ";
		$sql .= "    a.V03_TODOUFUKEN_CD, ";
		$sql .= "    a.V03_TODOUFUKEN || a.V03_SHIKUCHOUSON AS SHOZAICHI ";
		$sql .= "FROM ";
		$sql .= "    SCHOOL_USER.V03SCHOOL a, ";
		$sql .= "    SCHOOL_USER.T67SCHOOL_TOUROKU b, ";
		$sql .= "    M05TENPO_INFO ";
		$sql .= "WHERE ";
		$sql .= "     a.V03_SCH_CD=b.T67_SCH_CD ";
		$sql .= " AND b.T67_STATUS='3' ";
		$sql .= " AND M05_TENPO_CD = B.T67_TENPO_CD(+) ";
		if (!empty($params['sch_name'])) {
			$sql .= " AND ";
			$sql .= "    (UPPER(a.V03_SCH_NAME) LIKE '%' || :sch_name || '%' ";
			$sql .= "      OR  ";
			$sql .= "    UPPER(a.V03_SCH_NAME) LIKE  '%' || :sch_name || '%' ) ";
			$db_params[':sch_name'] = $params['sch_name'];
		}
		if (!empty($params['janru_cd'])) {
			$sql .= " AND a.V03_JANRU_CD = :janru_cd ";
			$db_params[':janru_cd'] = $params['janru_cd'];
		}
		if (!empty($params['PrefCd'])) {
			$prefCds = array();
			if (is_array($params['PrefCd'])) {
				$prefCds = $params['PrefCd'];
			} else {
				$prefCds[] = $params['PrefCd'];
			}
			$prefCds = array_filter($prefCds);
			$prefWhere = array();
			foreach ($prefCds as $key => $prefCd) {
				$prefWhere[] = "a.V03_TODOUFUKEN_CD = :pref_cd_{$key}";
				$db_params["pref_cd_{$key}"] = $prefCd;
			}
			if (!empty($prefWhere)) {
				$sql .= " AND (" . implode(" OR ", $prefWhere) . ")";
			}
		}

		$sql .= "GROUP BY ";
		$sql .= "    b.T67_SCH_CD, ";
		$sql .= "    a.V03_SCH_NAME, ";
		$sql .= "    a.V03_JANRU_CD, ";
		$sql .= "    a.V03_TODOUFUKEN_CD, ";
		$sql .= "    a.V03_TODOUFUKEN, ";
		$sql .= "    a.V03_SHIKUCHOUSON ";
		$sql .= "ORDER BY ";
		$sql .= "    a.V03_JANRU_CD DESC, ";
		$sql .= "    a.V03_TODOUFUKEN_CD, ";
		$sql .= "    a.V03_SCH_NAME ";
		return $sql;
	}

	/**
	* 学校名一覧データ取得（地図検索）
	*/
	public function getGakkoMapData($params)
	{
		$db_params = array();
		$sql = $this->generateGakkoMapSql($params,$db_params);
		return $this->dbGetList($sql,$db_params);
	}

   /**
	* 学校名一覧データを表示用に変換（地図検索）
	*
	* @access	public
	* @param	array ※参照渡し
	* @return	void
	*/
	public function convertGakkoMapData(&$data)
	{
		$data['Mapping'] = array();

		foreach ($data['gakko'] as $gakkoData)
		{
			$schCd = $gakkoData['M90_LANDMARK_CD'];

			// 学校CDが異なる場合
			if (array_key_exists($schCd, $data['Mapping']) == false)
			{
				$data['Mapping']["$schCd"]['SCH_CD']    = $schCd;
				$data['Mapping']["$schCd"]['SCH_NAME']  = $gakkoData['M90_LANDMARK_NAME'];
				$data['Mapping']["$schCd"]['SHOZAICHI'] = $gakkoData['M90_JYUUSHO_TEXT'];
				$data['Mapping']["$schCd"]['JANRU_CD']  = $gakkoData['M90_BUNRUI_CD'];
				$data['Mapping']["$schCd"]['PREF_CD']   = $gakkoData['M90_TODOUFUKEN_CD'];
				$data['Mapping']["$schCd"]['IDO']       = number_format($gakkoData['M90_IDO']/3600000,8);
				$data['Mapping']["$schCd"]['KEIDO']     = number_format($gakkoData['M90_KEIDO']/3600000,8);
			}
		}
	}

    /**
     * 学校名データ検索用SQL文の取得（地図検索）
     *
     * 学校名一覧データ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 町名データ検索用SQL
     */
    public function generateGakkoMapSql($params,&$db_params)
    {
    	$db_params[':sch_name'] = $params['sch_name'];

		$sql = "";
		$sql .= "SELECT ";
		$sql .= "     M90_LANDMARK_CD ";
		$sql .= "    ,M90_BUNRUI_CD ";
		$sql .= "    ,M90_LANDMARK_NAME ";
		$sql .= "    ,M90_JYUUSHO_TEXT ";
		$sql .= "    ,M90_TODOUFUKEN_CD ";
		$sql .= "    ,M90_IDO ";
		$sql .= "    ,M90_KEIDO ";
		$sql .= "FROM ";
		$sql .= "    M90LANDMARK ";
		$sql .= "WHERE ";
		$sql .= "    M90_LANDMARK_NAME LIKE '%' || :sch_name || '%' ";
		$sql .= "      OR  ";
		$sql .= "    M90_LANDMARK_NAME_KANA LIKE '%' || :sch_name || '%' ";
		$sql .= "ORDER BY ";
		$sql .= "     M90_TODOUFUKEN_CD ";
		$sql .= "    ,M90_BUNRUI_CD ";
		$sql .= "    ,M90_LANDMARK_NAME_KANA ";
        return $sql;
    }

}

?>